set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;
/*
应交件=已交件（原）+没有交件的（原）
已交件=已交件（原）+没有交件的（后续有操作的）
滞留件=应交件-已交件 =没有交件的（原）-没有交件的（后续有操作的）
明细滞留件标识=没有交件&没有后续操作
没有后续操作 集散点实际到件时间或者集散点实际发件时间小于其他操作时间 且 其他操作网点！= 集散点
*/
with waybill_ordersource as
(select
      waybill_no as billcode
     ,ordersource_code
     ,ordersource_name
 from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
 where dt between date_add('{{ execution_date | cst_ds }}', -20) and '{{ execution_date | cst_ds }}'
)
  ,take_out_detail as (  --取交件明细
    select
         intime.waybill       --运单
        ,intime.taking_date   --揽收时间
        ,intime.region_code   --管理大区编码
        ,intime.region_name   --管理大区名称
        ,intime.receive_proxy_code  --收件代理区编码
        ,intime.receive_proxy       --收件代理区名称
        ,intime.receive_franchisee_code  --收件加商商编码
        ,intime.receive_franchisee       --收件加商名称
        ,intime.receive_network_code     --收件网点
        ,intime.receive_network_name      --收件网点名称
        ,intime.actual_collect_code      --实际交件集散点编码
        ,intime.actual_collect_name      --实际交件集散点名称
        ,intime.plan_collect_arr_time  --规划到达集散点时间
        ,intime.actual_send_time         --网点发件时间
        ,intime.actual_collect_arr_time  --集点点实际到件时间
        ,intime.is_arr_collect_intime   --到达集散点是准点
        ,intime.is_over --0为准点交接票数
        ,if(intime.receive_proxy_code!=net.agent_code,1,0) as is_cross_agent --是否跨代区区
        ,if(reb.waybill_no is not null,1,0) as  is_reaback --是否是退转件
        ,if(t1.waybill_no is not null,1,0 ) as  is_end --是否完结件
        ,t2.scan_time as actual_collect_send_time
        ,nvl(intime.actual_collect_arr_time,t2.scan_time) as tmp_time
    from jms_dm.dm_waybill_collect_intime  intime    --交件准点率表
    left join jms_dim.dim_network_whole_massage net  --网点表
    on intime.actual_collect_code=net.code
    left join
    (--获取退转件
        select waybill_no
        from jms_dwd.dwd_tab_reback_transfer_express_base
        where  date(apply_time) between date_add('{{ execution_date | cst_ds }}',-9) and '{{ execution_date | cst_ds }}'
        group by waybill_no
    )reb on  reb.waybill_no = intime.waybill
    left join
    (
       select waybill_no
       from jms_dwd.dwd_tab_end_piece_base
       where   is_delete!=1 and input_time between date_add('{{ execution_date | cst_ds }}',-9) and '{{ execution_date | cst_ds }}'
       group by waybill_no
    )t1 on  intime.waybill =t1.waybill_no
    left join
    ( --集散点发件时间取
       select waybill_no ,min(scan_time) scan_time ,scan_site_code
       from jms_dwd.dwd_tab_barscan_sitesend_base_dt
       where dt between date_add('{{ execution_date | cst_ds }}',-9) and '{{ execution_date | cst_ds }}'
           and network_type =3 --集散点
       group by waybill_no,scan_site_code
    )t2 on  intime.waybill =t2.waybill_no and intime.actual_collect_code =t2.scan_site_code
    where intime.dt between date_add('{{ execution_date | cst_ds }}',-9) and date_add('{{ execution_date | cst_ds }}',-1)
    and  intime.actual_collect_code is not null--交件集散点不为空
    and  intime.is_over is not null and intime.type = 0   --应交接票数
    and  t1.waybill_no is null  --剔除完结件
),
other_track_waybill as ( --运单是否有其他轨迹
select waybill_no ,scan_type ,scan_time,network_code
from (
   select
   t2.waybill_no ,scan_type ,scan_time,network_code,
   ROW_NUMBER() over(partition by waybill_no order by scan_time) as rk
   from take_out_detail main
   inner join
   (
    select billcode as waybill_no,scan_type ,scan_time,network_code
    --from jms_dwd.dwd_s03_barscan_operations_hist_dt
       from jms_dwd.dwd_wide_barscan_operations_hist_dt
       where dt between  date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
             and scan_type in ('centerArrival','centerSend','siteArrival','siteSend','outStore','sign')
   )t2 on  main.waybill =t2.waybill_no  and  main.tmp_time <t2.scan_time  and main.actual_collect_code!=t2.network_code
)t where rk=1
)

--select * from take_out_detail

insert overwrite table jms_dm.dm_collect_ready_intime_detail_dt partition(dt)
select
     main.waybill   as waybill_no  --运单
    ,main.taking_date   --揽收时间
    ,main.region_code   --管理大区编码
    ,main.region_name   --管理大区名称
    ,main.receive_proxy_code  --收件代理区编码
    ,main.receive_proxy       --收件代理区名称
    ,main.receive_franchisee_code  --收件加商商编码
    ,main.receive_franchisee       --收件加商名称
    ,main.receive_network_code     --收件网点
    ,main.receive_network_name      --收件网点名称
    ,main.actual_collect_code      --实际交件集散点编码
    ,main.actual_collect_name      --实际交件集散点名称
    ,main.actual_send_time         --网点发件时间
    ,main.actual_collect_arr_time  --集点点实际到件时间
    ,main.is_arr_collect_intime   --到达集散点是准点
    ,main.is_cross_agent
    ,main.is_reaback
    ,main.is_end
    ,if(is_over=0 or standby.network_code is not null ,0,1) as is_detained --是否是滞留件
    ,if(is_over=0 or standby.network_code is not null ,1,0) as is_delivered --是否已交件
    ,standby.scan_type flow_scan_type  --后续操作扫描类型
    ,standby.scan_time flow_scan_time  --后续操作扫描时间
    ,standby.network_code flow_network_code --后续操作网点编码
    ,os.ordersource_name as order_source
    ,date(main.taking_date) as date_time
    ,main.plan_collect_arr_time    --规划到达集散点时间
    ,date(main.taking_date) as dt
from take_out_detail main
left join other_track_waybill standby on main.waybill=standby.waybill_no
left join waybill_ordersource os on os.billcode=main.waybill
distribute by pmod(hash(rand()), 16);
